package com.scalar.chiptrack.adaptor.ikanos.stats;

import com.scalar.chiptrack.jobs.JobHandler;
import com.scalar.chiptrack.adaptor.AdaptorConstants;
import com.scalar.chiptrack.adaptor.AdaptorUtils;
import com.scalar.chiptrack.adaptor.TestLotBinReport;
import com.scalar.chiptrack.adaptor.ShippedLot;
import com.scalar.chiptrack.adaptor.utils.ParserResult;
import com.scalar.chiptrack.adaptor.utils.Parser;
import com.scalar.chiptrack.adaptor.utils.ParserFactory;
import com.scalar.chiptrack.adaptor.utils.XLSParserImpl;
import com.scalar.chiptrack.adaptor.utils.CSVParserImpl;
import com.scalar.chiptrack.adaptor.dao.AssemblyAdaptorDAO;
import com.scalar.chiptrack.adaptor.dao.TestAdaptorDAO;
import com.scalar.chiptrack.adaptor.dao.InventoryLotDAO;
import com.scalar.chiptrack.adaptor.dao.FabSortAdaptorDAO;
import com.scalar.chiptrack.adaptor.dao.ShippedLotDAO;
import com.scalar.chiptrack.adaptor.dao.AdaptorRunInfo;
import com.scalar.chiptrack.utils.Logger;
import com.scalar.chiptrack.utils.StringUtils;
import com.scalar.ScalarException;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.TimeZone;
import java.util.Locale;
import java.util.GregorianCalendar;
import java.util.HashMap;
import java.io.File;

import jxl.Sheet;
import jxl.Workbook;
import jxl.Cell;
import jxl.CellType;
import jxl.DateCell;

/**
 * User: ChandraSekhar
 * Date: Aug 18, 2008
 * Time: 3:21:02 PM
 */
public class STATSPOP3DataParser extends JobHandler implements AdaptorConstants
{
    private HashMap<String, ShippedLot> m_fgShipDataMap = new HashMap<String, ShippedLot>();
    private static Logger m_parserLogger    = null;
    private String m_dataOutputDir          = null;

    public STATSPOP3DataParser() throws ScalarException
    {
        try
        {
            m_dataOutputDir = AdaptorUtils.getDataOutputDir( VENDOR_NAME_SCS, LOCATION_TYPE_TEST );
            AdaptorUtils.makeAllAdaptorDirs( m_dataOutputDir );
            m_parserLogger = AdaptorUtils.getDataParserLogger( m_dataOutputDir,VENDOR_NAME_SCS );
            AssemblyAdaptorDAO.setLogger( m_parserLogger );   // All the DAOs and Parser should be logging into same file
            TestAdaptorDAO.setLogger( m_parserLogger);
            InventoryLotDAO.setLogger( m_parserLogger );
            FabSortAdaptorDAO.setLogger( m_parserLogger );
            ShippedLotDAO.setLogger( m_parserLogger );
            AdaptorUtils.setLogger( m_parserLogger );
        }
        catch (ScalarException se)
        {
            log("ERROR!! ERROR!! ERROR!! Exception while loading/creating SCS output directory from database, Exception: " + se);
            throw se;
        }
    }
    /**
     *
     * @param args
     * @throws Exception
     */
    public static void main( String args[] ) throws Exception
    {
        try
        {
            new STATSPOP3DataParser().execute();
        }
        catch (Exception e)
        {
            throw e;
        }
    }
    //Execute Method
   public void execute() throws Exception
   {
       AdaptorUtils.printHeader(VENDOR_NAME_SCS + "Yield", CONSTANT_PARSER );

       File doneDestDir = new File( m_dataOutputDir + "/done" );

       File wipDataoutDir = new File( m_dataOutputDir );

       File[] fileList = wipDataoutDir.listFiles();

       if ( ( fileList == null ) || ( fileList.length <= 3 ) )
       {
           log( "No file found in directory: " + wipDataoutDir );
           throw new ScalarException( "No file found in directory: " + wipDataoutDir + "----\n\n" );
       }

       ArrayList<File> successFileList   = new ArrayList<File>();
       boolean success             = false;

       try
       {
           for ( int i = 0; i < fileList.length; i++ )
           {
               if ( fileList[i].isDirectory() == true )
               {
                   continue;
               }
               //System.out.println("fileList[i].getName()  = " + fileList[i].getName());
               if ( fileList[i].getName() != null && fileList[i].getName().trim().startsWith(STATSPOP3Adaptor.FILE_NAME_YIELD ) )
               {
                    ArrayList<TestLotBinReport> testBinList = parseFTYieldReport( fileList[i].getAbsolutePath() );

                   if ( testBinList != null && testBinList.size() > 0 )
                    {
                        //to get the SCS Test Bin Details
                        if ( testBinList.isEmpty() == false )
                        {   
                            log( "Updating SCS Binning Information" );
                            TestAdaptorDAO.saveTestBinInfo ( null, testBinList , VENDOR_NAME_SCS );
                            success = true;
                        }
                        else
                        {
                            log( "SCS Binning Information List is Empty" );
                        }
                    }

                   successFileList.add( fileList[i] );
               }
               //SCC YIELD
               if ( fileList[i].getName() != null && fileList[i].getName().trim().startsWith(STATSPOP3Adaptor.FILE_NAME_SCC_YIELD ) )
               {
                    ArrayList<TestLotBinReport> testBinList = parseSCCFTYieldReport( fileList[i].getAbsolutePath() );

                   if ( testBinList != null && testBinList.size() > 0 )
                    {
                        //to get the SCS Test Bin Details
                        if ( testBinList.isEmpty() == false )
                        {
                            log( "Updating SCC Binning Information" );
                            TestAdaptorDAO.saveTestBinInfo ( null, testBinList , VENDOR_NAME_SCC );
                        }
                        else
                        {
                            log( "SCC Binning Information List is Empty" );
                        }
                    }

                   successFileList.add( fileList[i] );
               }
               if ( fileList[i].getName() != null && fileList[i].getName().trim().startsWith(STATSPOP3Adaptor.FILE_NAME_SCK_YIELD ) )
               {
                    ArrayList<TestLotBinReport> testBinList = parseSCKFTYieldReport( fileList[i].getAbsolutePath() );

                    if ( testBinList != null && testBinList.size() > 0 )
                    {
                        //to get the SCS Test Bin Details
                        if ( testBinList.isEmpty() == false )
                        {
                            log( "Updating SCK Binning Information" );
                            TestAdaptorDAO.saveTestBinInfo ( null, testBinList , VENDOR_NAME_SCK );
                        }
                        else
                        {
                            log( "SCK Binning Information List is Empty" );
                        }
                    }

                   successFileList.add( fileList[i] );
               }
               if ( fileList[i].getName() != null && fileList[i].getName().trim().startsWith(STATSPOP3Adaptor.FILE_NAME_SCM_YIELD ) )
               {
                    ArrayList<TestLotBinReport> testBinList = parseSCMFTYieldReport( fileList[i].getAbsolutePath() );

                   if ( testBinList != null && testBinList.size() > 0 )
                    {
                        //to get the SCM Test Bin Details
                        if ( testBinList.isEmpty() == false )
                        {
                            log( "Updating SCM Binning Information" );
                            TestAdaptorDAO.saveTestBinInfo ( null, testBinList , VENDOR_NAME_SCM );
                        }
                        else
                        {
                            log( "SCM Binning Information List is Empty" );
                        }
                    }

                   successFileList.add( fileList[i] );
               }
               if ( fileList[i].getName() != null && fileList[i].getName().trim().startsWith( STATSPOP3Adaptor.FILE_NAME_DROPSHIP_SCS )
				   && fileList[i].getName().trim().toUpperCase().indexOf(".TXT") == -1 )
               {
                    parseDropShipReport( fileList[i].getAbsolutePath() );
                                                                                  
                   successFileList.add( fileList[i] );
               }
               if ( fileList[i].getName() != null && fileList[i].getName().trim().startsWith( STATSPOP3Adaptor.FILE_NAME_DROPSHIP_SCK )
				   && fileList[i].getName().trim().toUpperCase().indexOf(".TXT") == -1 )
               {
                    parseSCKDropShipReport( fileList[i].getAbsolutePath() );

                   successFileList.add( fileList[i] );
               }
               if ( fileList[i].getName() != null && fileList[i].getName().trim().startsWith( STATSPOP3Adaptor.FILE_NAME_DROPSHIP_SCM )
				   && fileList[i].getName().trim().toUpperCase().indexOf(".TXT") == -1 )
               {
                    parseSCMDropShipReport( fileList[i].getAbsolutePath() );

                   successFileList.add( fileList[i] );
               }
               success = true;
           }
       }
       catch ( Exception e )
       {
           success = false;
           log( "ERROR!! ERROR!! ERROR!! While parsing and storing File " + e );
           e.printStackTrace();
           throw e;
       }

       try
        {
            // Save FG Ship Information
            if ( m_fgShipDataMap.isEmpty() == false )
            {
                log( " Updating FG Shiping Lot Information" );
                ShippedLotDAO.saveShippedLot( null, m_fgShipDataMap.values().iterator() );
                success = true;
            }
            m_fgShipDataMap.clear();
            m_fgShipDataMap = null;
        }
        catch (ScalarException se)
        {
            success = false;
            se.printStackTrace();
            log( "ERROR!! ERROR!! ERROR!! Could not update FG Shipping Lot information into database: " + se );
            throw se;
        }
      // TO SEND THE NEW LOTS, NEW TRANSIT LOTS, NO PO LOTS, DISCREPANCY LOTS NOTIFICATIONS TO THE USER
      //AdaptorDAO.sendAllNotifications( VENDOR_NAME_SCS, true, true, true );
      //AdaptorDAO.sendAllNotifications( VENDOR_NAME_SCK, true, true, true );

      // To Close the Drop Out Lots
      //AdaptorDAO.closeDropOutLots( VENDOR_NAME_SCS );

      int adaptor_id = AdaptorRunInfo.getLastRunAdaptorId( STATSPOP3Adaptor.ADAPTOR_NAME, CONSTANT_ADAPTOR);

      // Update Adaptor_Run_Info table
      try
      {
            if ( success == true )
            {
                AdaptorRunInfo.saveAdaptorLastUpdatedStatus(CONSTANT_PARSER, STATSPOP3Adaptor.ADAPTOR_NAME, CONSTANT_SUCCESS, adaptor_id);
                System.out.println( "SUCCESSFULLY UPDATED IN THE DATABASE" );
            }
      }
      catch ( ScalarException se )
      {
            success = false; //As filES DATA SAVED , UPDATEING AS SUCCESS
            AdaptorRunInfo.saveAdaptorLastUpdatedStatus(CONSTANT_PARSER, STATSPOP3Adaptor.ADAPTOR_NAME, CONSTANT_SUCCESS, adaptor_id);
            log( "WARNING!! WARNING!! WARNING!! Could not update Adaptor run information, Exception: " + se );
            throw se;
       }
        // Move the successful files to 'done' directory
       if ( success )
       {
           AdaptorUtils.moveFilesToDone( successFileList, doneDestDir );
       }
   }//end of execute method

    private ArrayList<TestLotBinReport> parseFTYieldReport( String fileName ) throws Exception
    {
        if ( fileName == null ) throw new ScalarException( "Filename argument is null" );

        ArrayList ftYieldList  = new ArrayList();
        TestLotBinReport ftYieldStatus  = null;

        try
        {
            File detailWipFile = new File( fileName );
            if ( detailWipFile.exists() == false )
            {
                throw new ScalarException( fileName + " doesnt exist" );
            }

            log( "\nFound " + fileName );
            log( "\nParsing the file for SCS FT Yield Info..." );
            System.out.println( "\nFound " + fileName );
            System.out.println( "\nParsing the file for SCS FT Yield Info..." );

            Workbook workbook = Workbook.getWorkbook( detailWipFile );
            Sheet sheet = workbook.getSheet( 0 );
            Cell[] row = null;

            for ( int rowCount = 1; rowCount < sheet.getRows(); rowCount++ )
            {
                row = sheet.getRow( rowCount );

                ftYieldStatus = new TestLotBinReport();
                //ROW 0, 1 not Required
                if( row.length > 2 )
                {
                    if ( row[2].getType().equals(CellType.EMPTY) )
                    {
                        ftYieldStatus.setDeviceName( null );
                    }
                    else
                    {
                        ftYieldStatus.setDeviceName( row[2].getContents().trim() );
                    }
                }
                //3
                if( row.length > 4 )
                {
                    if ( row[4].getType().equals(CellType.EMPTY) )
                    {
                        ftYieldStatus.setPkgType( null );
                    }
                    else
                    {
                        ftYieldStatus.setPkgType( row[4].getContents().trim() );
                    }
                }
                if( row.length > 5)
                {
                    if ( row[5].getType().equals(CellType.EMPTY) )
                    {
                        ftYieldStatus.setLeadCount( null );
                    }
                    else
                    {
                        ftYieldStatus.setLeadCount(row[5].getContents().trim());
                    }
                }
                if( row.length > 6)
                {
                    if ( row[6].getType().equals(CellType.EMPTY) )
                    {
                        ftYieldStatus.setCustLotNo( null );
                    }
                    else
                    {
                        ftYieldStatus.setCustLotNo( row[6].getContents().trim());
                    }
                }

                if ( row.length > 7 )
                {
                   if ( row[7].getType().equals(CellType.EMPTY) )
                    {
                        ftYieldStatus.setLotNumber( null );
                    }
                    else
                    {
                        ftYieldStatus.setLotNumber( row[7].getContents().trim());
                    }
                }
                 //Row  8, 9,10
                if( row.length > 11 )
                {
                    if ( row[11].getType().equals(CellType.EMPTY) )
                    {
                        ftYieldStatus.setTesterId( null );
                    }
                    else
                    {
                        ftYieldStatus.setTesterId( row[11].getContents().trim());
                    }
                }
                //12
                if( row.length > 13 )
                {
                    if ( row[13].getType().equals(CellType.EMPTY) )
                    {
                        ftYieldStatus.setHandler( null );
                    }
                    else
                    {
                        ftYieldStatus.setHandler( row[13].getContents().trim());
                    }
                }
                // 14
                if( row.length >15 )
                {
                    if ( row[15].getType().equals(CellType.EMPTY) )
                    {
                        ftYieldStatus.setLb( null );
                    }
                    else
                    {
                        ftYieldStatus.setLb( row[15].getContents().trim());
                    }

                }
                 if( row.length >16 )
                {
                    if ( row[16].getType().equals(CellType.EMPTY) )
                    {
                        ftYieldStatus.setTestProg( null );
                    }
                    else
                    {
                        ftYieldStatus.setTestProg( row[16].getContents().trim());
                    }
                }
                //17
                if( row.length >18 )
                {
                    if ( row[18].getType().equals(CellType.EMPTY) )
                    {
                        ftYieldStatus.setTemp( null );
                    }
                    else
                    {
                        ftYieldStatus.setTemp( row[18].getContents().trim());
                    }
                }

                if( row.length >19 )
                {
                    if ( row[19].getType().equals(CellType.EMPTY) )
                    {
                        ftYieldStatus.setStage( null );
                    }
                    else
                    {
                        ftYieldStatus.setStage( row[19].getContents().trim());
                    }
                }
                if( row.length >20 )
                {
                    if ( row[20].getType().equals(CellType.EMPTY) )
                    {
                        ftYieldStatus.setDateCode( null );
                    }
                    else
                    {
                        ftYieldStatus.setDateCode( row[20].getContents().trim());
                    }
                }
                if( row.length >21 )
                {
                    if ( row[21].getType().equals(CellType.EMPTY) )
                    {
                        ftYieldStatus.setDateIn( null );
                    }
                    else if( row[21].getType().equals( CellType.DATE ) )
                    {
                        DateCell dc = (DateCell) row[21];
                        java.util.Date dateIn = null;
                        dateIn = dc.getDate();

                        if ("America/Los_Angeles".equals(System.getProperty("user.timezone")))
                        {
                            Calendar cal = Calendar.getInstance( TimeZone.getTimeZone( "GMT" ), Locale.US );
                            cal.setTime(dateIn);
                            dateIn = new GregorianCalendar(cal.get(Calendar.YEAR), cal.get(Calendar.MONTH), cal.get(Calendar.DATE)).getTime();
                            ftYieldStatus.setDateIn( StringUtils.formatDateToString( dateIn )  ) ;
                        }
                        else
                        {
                            Calendar cal = new GregorianCalendar();
                            cal.setTime(dateIn);
                            dateIn = new GregorianCalendar(cal.get(Calendar.YEAR), cal.get(Calendar.MONTH), cal.get(Calendar.DATE)).getTime();
                            ftYieldStatus.setDateIn(StringUtils.formatDateToString(dateIn));
                        }
                    }
                    else
                    {
                        ftYieldStatus.setDateIn( row[21].getContents().trim().split(" ")[0]);
                    }
                }
                if( row.length >22 )
                {
                    if ( row[22].getType().equals(CellType.EMPTY) )
                    {
                        ftYieldStatus.setDateOut( null );
                    }
                    else if( row[22].getType().equals( CellType.DATE ) )
                    {
                        DateCell dc = (DateCell) row[22];
                        java.util.Date dateIn = null;
                        dateIn = dc.getDate();

                        if ("America/Los_Angeles".equals(System.getProperty("user.timezone")))
                        {
                            Calendar cal = Calendar.getInstance( TimeZone.getTimeZone( "GMT" ), Locale.US );
                            cal.setTime(dateIn);
                            dateIn = new GregorianCalendar(cal.get(Calendar.YEAR), cal.get(Calendar.MONTH), cal.get(Calendar.DATE)).getTime();
                            ftYieldStatus.setDateOut( StringUtils.formatDateToString( dateIn )  ) ;
                        }
                        else
                        {
                            Calendar cal = new GregorianCalendar();
                            cal.setTime(dateIn);
                            dateIn = new GregorianCalendar(cal.get(Calendar.YEAR), cal.get(Calendar.MONTH), cal.get(Calendar.DATE)).getTime();
                            ftYieldStatus.setDateOut(StringUtils.formatDateToString(dateIn));
                        }
                    }
                    else
                    {
                        ftYieldStatus.setDateOut( row[22].getContents().trim().split(" ")[0]);
                    }
                }
                if( row.length > 23 )
                {
                    if ( row[23].getType().equals(CellType.EMPTY) )
                    {
                        ftYieldStatus.setInQty( null );
                    }
                    else
                    {
                        ftYieldStatus.setInQty( row[23].getContents().trim());
                    }
                }
                if( row.length > 24 )
                {
                    if ( row[24].getType().equals(CellType.EMPTY) )
                    {
                        ftYieldStatus.setPassQty( null );
                    }
                    else
                    {
                        ftYieldStatus.setPassQty( row[24].getContents().trim());
                    }
                }
                if( row.length > 25 )
                {
                    if ( row[25].getType().equals(CellType.EMPTY) )
                    {
                        ftYieldStatus.setYield( null );
                    }
                    else
                    {
                        ftYieldStatus.setYield( row[25].getContents().trim().replaceAll("%", ""));
                    }
                }
                //26
                if( row.length > 27 )
                {
                    if ( row[27].getType().equals(CellType.EMPTY) )
                    {
                        ftYieldStatus.setOsQty( null );
                    }
                    else
                    {
                        ftYieldStatus.setOsQty( row[27].getContents().trim());
                    }
                }
                if( row.length > 28 )
                {
                    if ( row[28].getType().equals(CellType.EMPTY) )
                    {
                        ftYieldStatus.setOSRate( null );
                    }
                    else
                    {
                        ftYieldStatus.setOSRate( row[28].getContents().trim().replaceAll("%", ""));
                    }
                }
                //29
                if( row.length > 30 )
                {
                    if ( row[30].getType().equals(CellType.EMPTY) )
                    {
                        ftYieldStatus.setBin1( null );
                    }
                    else
                    {
                        ftYieldStatus.setBin1( row[30].getContents().trim() );
                    }
                }
                if( row.length > 31 )
                {
                    if ( row[31].getType().equals(CellType.EMPTY) )
                    {
                        ftYieldStatus.setBin2( null );
                    }
                    else
                    {
                        ftYieldStatus.setBin2( row[31].getContents().trim() );
                    }
                }
                if( row.length > 32 )
                {
                    if ( row[32].getType().equals(CellType.EMPTY) )
                    {
                        ftYieldStatus.setBin3( null );
                    }
                    else
                    {
                        ftYieldStatus.setBin3( row[32].getContents().trim());
                    }
                }
                if( row.length > 33 )
                {
                    if ( row[33].getType().equals(CellType.EMPTY) )
                    {
                        ftYieldStatus.setBin4( null );
                    }
                    else
                    {
                        ftYieldStatus.setBin4( row[33].getContents().trim());
                    }
                }
                if( row.length > 34 )
                {
                    if ( row[34].getType().equals(CellType.EMPTY) )
                    {
                        ftYieldStatus.setBin5( null );
                    }
                    else
                    {
                        ftYieldStatus.setBin5( row[34].getContents().trim());
                    }
                }
                if( row.length > 35)
                {
                    if ( row[35].getType().equals(CellType.EMPTY) )
                    {
                        ftYieldStatus.setBin6( null );
                    }
                    else
                    {
                        ftYieldStatus.setBin6( row[35].getContents().trim());
                    }
                }
                if( row.length > 36 )
                {
                    if ( row[36].getType().equals(CellType.EMPTY) )
                    {
                        ftYieldStatus.setBin7( null );
                    }
                    else
                    {
                        ftYieldStatus.setBin7( row[36].getContents().trim());
                    }
                }
                if( row.length > 37 )
                {
                    if ( row[37].getType().equals(CellType.EMPTY) )
                    {
                        ftYieldStatus.setBin8( null );
                    }
                    else
                    {
                        ftYieldStatus.setBin8( row[37].getContents().trim());
                    }
                }
                if( row.length > 38 )
                {
                    if ( row[38].getType().equals(CellType.EMPTY) )
                    {
                        ftYieldStatus.setBin9( null );
                    }
                    else
                    {
                        ftYieldStatus.setBin9( row[38].getContents().trim());
                    }
                }
                if( row.length > 39 )
                {
                    if ( row[39].getType().equals(CellType.EMPTY) )
                    {
                        ftYieldStatus.setBin10( null );
                    }
                    else
                    {
                        ftYieldStatus.setBin10( row[39].getContents().trim());
                    }
                }
                if( row.length > 40 )
                {
                    if ( row[40].getType().equals(CellType.EMPTY) )
                    {
                        ftYieldStatus.setVmQty( null );
                    }
                    else
                    {
                        ftYieldStatus.setVmQty( row[40].getContents().trim());
                    }
                }
                if( row.length > 41 )
                {
                    if ( row[41].getType().equals(CellType.EMPTY) )
                    {
                        ftYieldStatus.setLossQty( null );
                    }
                    else
                    {
                        ftYieldStatus.setLossQty( row[41].getContents().trim());
                    }
                }

                ftYieldList.add( ftYieldStatus );
            }
        }
        catch ( Exception ex )
        {   
			System.out.println("ERROR!!!ERROR!!! While Parsing the SCS FT Month Yield Report");
            ex.printStackTrace();
            throw ex;
        }

        return ftYieldList;
    }
    private ArrayList<TestLotBinReport> parseSCCFTYieldReport( String fileName ) throws Exception
    {
        if ( fileName == null ) throw new ScalarException( "Filename argument is null" );
        ParserResult result     = null;

        String lotNumber        = null;
        String partNumber       = null;
        String fablotNumber     = null;
        ArrayList<TestLotBinReport> ftYieldList  = new ArrayList<TestLotBinReport>();

        try
        {
            File detailWipFile = new File( fileName );
            if ( detailWipFile.exists() == false )
            {
                throw new ScalarException( fileName + " doesnt exist" );
            }

            log( "\nFound " + fileName );
            log( "\nParsing the file for SCC Ft Yield Report..." );
            System.out.println( "\nFound " + fileName );
            System.out.println( "\nParsing the file for SCC FT Yield Report..." );

            Parser parser = ParserFactory.getXLSParser( detailWipFile );

            ((XLSParserImpl)parser).setHeaderRowIdx(0);
            ((XLSParserImpl)parser).setDataStartRowIdx(1);

            result = parser.parse();

            //System.out.println("result.getString() = " + result.getHeadings() );

            while ( result.next() )
            {
                partNumber   = result.getString("Customer Device");
                fablotNumber = result.getString("Lot No");
                lotNumber    = result.getString("Lot No");
                if ( lotNumber == null)
                {
                    lotNumber = fablotNumber;
                }

                if ( partNumber != null && partNumber.length() > 0 && lotNumber != null && lotNumber.length() > 0 )
                {
                    TestLotBinReport ftYieldStatus = new TestLotBinReport();

                    ftYieldStatus.setDeviceName( partNumber );
                    ftYieldStatus.setLotNumber( lotNumber );
                    ftYieldStatus.setCustLotNo( fablotNumber );
                    //ftYieldStatus.setPoNumber( result.getString("Release No"));
                    ftYieldStatus.setPkgType( result.getString("Package") );
                    ftYieldStatus.setLeadCount( result.getString("Lead Count") );
                    ftYieldStatus.setStage( result.getString("Insertion") ); // Insertion
                    ftYieldStatus.setTestProg(result.getString("Test Program"));
                    ftYieldStatus.setTesterId( result.getString("Tester") );
                    ftYieldStatus.setDateCode( result.getString("Date Code") );
                    ftYieldStatus.setHandler( result.getString("Handler") );
                    ftYieldStatus.setLb(result.getString("L/B#"));
                    ftYieldStatus.setTemp( result.getString("Temperature"));
                    ftYieldStatus.setDateIn( StringUtils.formatDateToString( result.getDate("Test In Date") ) );
                    ftYieldStatus.setDateOut(StringUtils.formatDateToString( result.getDate("Test Out Date") ) );
                    ftYieldStatus.setInQty( result.getInt("In Qty") +"");
                    ftYieldStatus.setPassQty( result.getInt("Good Qty")+"" );
                    //ftYieldStatus.setDamageQty( result.getString("Defect Qty") );
                    ftYieldStatus.setYield( result.getString("Yield").replaceAll("%",""));
                    ftYieldStatus.setBin1( result.getString("Bin1") );
                    ftYieldStatus.setBin2(result.getString("Bin2"));
                    ftYieldStatus.setBin3(result.getString("Bin3"));
                    ftYieldStatus.setBin4(result.getString("Bin4"));
                    ftYieldStatus.setBin5(result.getString("Bin5"));
                    ftYieldStatus.setBin6(result.getString("Bin6"));
                    ftYieldStatus.setBin7(result.getString("Bin7"));
                    ftYieldStatus.setBin8(result.getString("Bin8"));

                    ftYieldStatus.setLossQty( result.getString("Missing Unit") );
                    ftYieldStatus.setVmQty( result.getString("B/L") );
                    ftYieldStatus.setOsQty( result.getString("O/S Qty") );
                    ftYieldStatus.setOSRate( result.getString("O/S Yield").replaceAll("%","") );

                    ftYieldList.add( ftYieldStatus );

                }
            }
        }
        catch ( Exception ex )
        {
            System.out.println("ERROR!!!ERROR!!! While Parsing the SCC FT Yield Report");
            ex.printStackTrace();
            throw ex;
        }

        return ftYieldList;
    }
    private ArrayList<TestLotBinReport> parseSCKFTYieldReport( String fileName ) throws Exception
    {
        if ( fileName == null ) throw new ScalarException( "Filename argument is null" );
        ParserResult result     = null;

        String lotNumber        = null;
        String partNumber       = null;
        String fablotNumber     = null;
        ArrayList<TestLotBinReport> ftYieldList  = new ArrayList<TestLotBinReport>();

        try
        {
            File detailWipFile = new File( fileName );
            if ( detailWipFile.exists() == false )
            {
                throw new ScalarException( fileName + " doesnt exist" );
            }

            log( "\nFound " + fileName );
            log( "\nParsing the file for SCK Ft Yield Report..." );
            System.out.println( "\nFound " + fileName );
            System.out.println( "\nParsing the file for SCK FT Yield Report..." );

            Parser parser = ParserFactory.getCSVParser( detailWipFile, ',' );

            ((CSVParserImpl)parser).setHeaderRowIdx(0);
            ((CSVParserImpl)parser).setDataStartRowIdx(1);

            result = parser.parse();

            //System.out.println("result.getString() = " + result.getHeadings() );

            while ( result.next() )
            {
                partNumber   = result.getString("Customer Device");
                fablotNumber = result.getString("Lot No");
                lotNumber    = result.getString("Lot No");
                if ( lotNumber == null)
                {
                    lotNumber = fablotNumber;
                }

                if ( partNumber != null && partNumber.length() > 0 && lotNumber != null && lotNumber.length() > 0 )
                {
                    TestLotBinReport ftYieldStatus = new TestLotBinReport();

                    ftYieldStatus.setDeviceName( partNumber );
                    ftYieldStatus.setLotNumber( lotNumber );
                    ftYieldStatus.setCustLotNo( fablotNumber );
                    //ftYieldStatus.setPoNumber( result.getString("Release No"));
                    ftYieldStatus.setPkgType( result.getString("Package") );
                    ftYieldStatus.setLeadCount( result.getString("Lead Count") );
                    ftYieldStatus.setStage( result.getString("Insertion") ); // Insertion
                    ftYieldStatus.setTestProg(result.getString("Test Program"));
                    ftYieldStatus.setTesterId( result.getString("Tester") );
                    ftYieldStatus.setDateCode( result.getString("Date Code") );
                    ftYieldStatus.setHandler( result.getString("Handler") );
                    ftYieldStatus.setLb(result.getString("L/B#"));
                    ftYieldStatus.setTemp( result.getString("Temperature"));

                    ftYieldStatus.setDateIn( StringUtils.formatDate(result.getString("Test In Date"), "yyyy/MM/dd HH:mm:ss", "MM/dd/yyyy HH:mm:ss" ) );
                    ftYieldStatus.setDateOut(StringUtils.formatDate( result.getString("Test Out Date"), "yyyy/MM/dd HH:mm:ss", "MM/dd/yyyy HH:mm:ss") );
                    ftYieldStatus.setInQty( result.getInt("IN Qty") +"");
                    if ( result.getInt("Good Qty") > 0 )
                    {
                        ftYieldStatus.setPassQty( result.getInt("Good Qty")+"" );
                    }
                    else
                    {
                        ftYieldStatus.setPassQty( result.getInt("Bin1")+"" );
                    }
                    //ftYieldStatus.setDamageQty( result.getString("Defect Qty") );
                    ftYieldStatus.setYield( result.getString("Yield").replaceAll("%",""));
                    ftYieldStatus.setBin1( result.getInt("Bin1")+"" );
                    ftYieldStatus.setBin2(result.getInt("Bin2")+"");
                    ftYieldStatus.setBin3(result.getInt("Bin3")+"");
                    ftYieldStatus.setBin4(result.getInt("Bin4")+"");
                    ftYieldStatus.setBin5(result.getInt("Bin5")+"");
                    ftYieldStatus.setBin6(result.getInt("Bin6")+"");
                    ftYieldStatus.setBin7(result.getInt("Bin7")+"");
                    ftYieldStatus.setBin8(result.getInt("Bin8")+"");
                    ftYieldStatus.setBin9(result.getInt("Bin9")+"");
                    ftYieldStatus.setBin10(result.getInt("Bin10")+"");

                    ftYieldStatus.setLossQty( result.getInt("Missing Unit")+"" );
                    ftYieldStatus.setVmQty( result.getInt("B/L")+"" );
                    ftYieldStatus.setOsQty( result.getInt("O/S Qty")+"" );
                    ftYieldStatus.setOSRate( result.getString("O/S Yield") == null ? "" : result.getString("O/S Yield").replaceAll("%","") );

                    ftYieldList.add( ftYieldStatus );

                }
            }
        }
        catch ( Exception ex )
        {
            System.out.println("ERROR!!!ERROR!!! While Parsing the SCK FT Yield Report");
            ex.printStackTrace();
            throw ex;
        }

        return ftYieldList;
    }
    private ArrayList<TestLotBinReport> parseSCMFTYieldReport( String fileName ) throws Exception
    {
        if ( fileName == null ) throw new ScalarException( "Filename argument is null" );
        ParserResult result     = null;

        String lotNumber        = null;
        String partNumber       = null;
        String fablotNumber     = null;
        ArrayList<TestLotBinReport> ftYieldList  = new ArrayList<TestLotBinReport>();

        try
        {
            File detailWipFile = new File( fileName );
            if ( detailWipFile.exists() == false )
            {
                throw new ScalarException( fileName + " doesnt exist" );
            }

            log( "\nFound " + fileName );
            log( "\nParsing the file for SCM Ft Yield Report..." );
            System.out.println( "\nFound " + fileName );
            System.out.println( "\nParsing the file for SCM FT Yield Report..." );

            Parser parser = ParserFactory.getXLSParser( detailWipFile );

            ((XLSParserImpl)parser).setHeaderRowIdx(0);
            ((XLSParserImpl)parser).setDataStartRowIdx(1);

            result = parser.parse();

            //System.out.println("result.getString() = " + result.getHeadings() );

            while ( result.next() )
            {
                partNumber   = result.getString("Customer Device");
                //fablotNumber = result.getString("Wafer Run");
                lotNumber    = result.getString("Lot No");
                if ( lotNumber == null)
                {
                    lotNumber = fablotNumber;
                }
				if ( fablotNumber == null )
                {
                    fablotNumber = lotNumber;
                }

                if ( partNumber != null && partNumber.length() > 0 && lotNumber != null && lotNumber.length() > 0 )
                {
                    TestLotBinReport ftYieldStatus = new TestLotBinReport();

                    ftYieldStatus.setDeviceName( partNumber );
                    ftYieldStatus.setLotNumber( lotNumber );
                    ftYieldStatus.setCustLotNo( fablotNumber );
                    //ftYieldStatus.setPoNumber( result.getString("Release No"));
                    ftYieldStatus.setPkgType( result.getString("Package") );
                    ftYieldStatus.setLeadCount( result.getString("Lead Count") );
                    ftYieldStatus.setStage( result.getString("Insertion") ); // Insertion
                    ftYieldStatus.setTestProg(result.getString("Test Program"));
                    ftYieldStatus.setTesterId( result.getString("Tester") );
                    ftYieldStatus.setDateCode( result.getString("Date Code") );
                    ftYieldStatus.setHandler( result.getString("Handler") );
                    ftYieldStatus.setLb(result.getString("L/B#"));
                    ftYieldStatus.setTemp( result.getString("Temperature"));
                    ftYieldStatus.setDateIn( StringUtils.formatDateToString( result.getDate("Test In Date") ) );
                    ftYieldStatus.setDateOut(StringUtils.formatDateToString( result.getDate("Test Out Date") ) );
                    ftYieldStatus.setInQty( result.getInt("In Qty") +"");
                    ftYieldStatus.setPassQty( result.getInt("Good Qty")+"" );
                    //ftYieldStatus.setDamageQty( result.getString("Defect Qty") );
                    ftYieldStatus.setYield( result.getString("Yield").replaceAll("%",""));
                    ftYieldStatus.setBin1( result.getString("BIN 1") );
                    ftYieldStatus.setBin2(result.getString("BIN 2"));
                    ftYieldStatus.setBin3(result.getString("BIN 3"));
                    ftYieldStatus.setBin4(result.getString("BIN 4"));
                    ftYieldStatus.setBin5(result.getString("BIN 5"));
                    ftYieldStatus.setBin6(result.getString("BIN 6"));
                    ftYieldStatus.setBin7(result.getString("BIN 7"));
                    ftYieldStatus.setBin8(result.getString("BIN 8"));
                    ftYieldStatus.setBin9(result.getString("BIN 9"));
                    ftYieldStatus.setBin10(result.getString("BIN 10"));

                    ftYieldStatus.setLossQty( result.getString("Missing Unit") );
                    ftYieldStatus.setVmQty( result.getString("B/L") );
                    ftYieldStatus.setOsQty( result.getString("O/S Qty") );
                    ftYieldStatus.setOSRate( result.getString("O/S Yield").replaceAll("%","") );

                    ftYieldList.add( ftYieldStatus );

                }
            }
        }
        catch ( Exception ex )
        {
            System.out.println("ERROR!!!ERROR!!! While Parsing the SCM FT Yield Report");
            ex.printStackTrace();
            throw ex;
        }

        return ftYieldList;
    }
    private void parseDropShipReport(  String fileName) throws Exception
    {
        if ( fileName == null ) throw new ScalarException( "Filename argument is null" );
        ParserResult result     = null;
        ShippedLot shippedLot   = null;
        String partNumber       = null;
        String lotNumber        = null;
        String shipDate         = null;
        String nextLocation     = null;

        try
        {
            File shipFile = new File( fileName );
            if ( shipFile.exists() == false )
            {
                throw new ScalarException( fileName + " doesnt exist" );
            }

            log( "\nFound " + fileName );
            log( "Parsing the file for Drop Shipment Report..." );
            System.out.println( "\nFound " + fileName );
            System.out.println( "Parsing the file for Drop Shipment Report..." );

            int headerRow = -1;

            Parser parser = ParserFactory.getXLSParser( shipFile );


            ArrayList sheetNamesList = ((XLSParserImpl)parser).getSheetNamesList();

            for(int i= 0; i< sheetNamesList.size(); i++ )
            {
                String sheetName = (String)sheetNamesList.get( i );

                ((XLSParserImpl)parser).setHeaderRowIdx( -1 );
                ((XLSParserImpl)parser).setDataStartRowIdx(0);

                if ( sheetName != null && sheetName.startsWith("PAC") )
                {
                     result = ((XLSParserImpl)parser).parse( sheetName );

                     //Reading the 7th row In the Sheet row index starts from "0"
                    result.moveTo( 6 );

                    try
                    {
                        shipDate = StringUtils.formatDateToString( result.getDate(8) );
                    }catch(RuntimeException re)
                    {
                        shipDate = StringUtils.formatDateToString( result.getDate(9) );
                    }

                    result.moveTo(8);
                    nextLocation = result.getString(6);


                    while ( result.next() )
                    {
                        if ( "MANF. PART NUMBER".equalsIgnoreCase( result.getString(2) ) )
                        {
                            headerRow =  result.getFileRowIdx();
                            break;
                        }
                    }
                    String poNumber = null;
                    String shipdocNo = null;
                    String soNo = null;
                    result.moveTo(headerRow-1);
                    if ( result.getString(0) != null )
                    {
                         poNumber = result.getString(0).substring( result.getString(0).indexOf(":") +1, result.getString(0).length() ).trim();
                    }
                    result.moveTo(headerRow-2);
                    if ( result.getString(0) != null )
                    {
                         shipdocNo = result.getString(0).substring( result.getString(0).indexOf(":")+1, result.getString(0).length() ).trim();
                    }
                    result.moveTo(headerRow-3);
                    if ( result.getString(0) != null )
                    {
                         soNo = result.getString(0).substring( result.getString(0).indexOf("#")+1, result.getString(0).length() ).trim();
                    }

                    ((XLSParserImpl)parser).setHeaderRowIdx(headerRow);
                    ((XLSParserImpl)parser).setDataStartRowIdx(headerRow+1);

                    result = ((XLSParserImpl)parser).parse( sheetName );

                    String mawb = null;
                    String hawb = null;
                    String filghtInfo = null;
                    String etd = null;
                    String eta = null;
                    while( result.next())
                    {
                        if ( result.getString("S/N") != null )
                        {
                            if (  result.getString("S/N").toUpperCase().startsWith("MAWB") )
                            {
                                if ( result.getString("S/N").indexOf(":") != -1 )
                                {
                                    mawb = result.getString("S/N").substring( result.getString("S/N").indexOf(":") +1, result.getString("S/N").length() );
                                }
                                else
                                {
                                   mawb = result.getString("MKT NO.");
                                }
                            }
                            if (  result.getString("S/N").toUpperCase().startsWith("HAWB") )
                            {
                                if ( result.getString("S/N").indexOf(":") != -1 )
                                {
                                    hawb = result.getString("S/N").substring( result.getString("S/N").indexOf(":") +1, result.getString("S/N").length() );
                                }
                                else
                                {
                                   hawb = result.getString("MKT NO.");
                                }
                            }
                            if (  result.getString("S/N").toUpperCase().startsWith("FLT") )
                            {
                                if ( result.getString("S/N").indexOf(":") != -1 )
                                {
                                    filghtInfo = result.getString("S/N").substring( result.getString("S/N").indexOf(":") +1, result.getString("S/N").length() );
                                }
                                else
                                {
                                   filghtInfo = result.getString("MKT NO.");
                                }
                            }
                            /*if (  result.getString("S/N").toUpperCase().startsWith("ETD") )
                            {
                                if ( result.getString("S/N").indexOf(":") != -1 )
                                {
									if(  )
                                    etd = result.getString("S/N").substring( result.getString("S/N").indexOf(":") +1, result.getString("S/N").indexOf(":") + 6 ).trim();
                                    eta = result.getString("S/N").substring( result.getString("S/N").lastIndexOf(":") +1, result.getString("S/N").lastIndexOf(":") +6 ).trim();

                                    etd = StringUtils.formatDate( shipDate + etd, "MM/dd/yyyyHHmm", "MM/dd/yyyy HH:mm:ss");
                                    eta = StringUtils.formatDate( shipDate + eta, "MM/dd/yyyyHHmm", "MM/dd/yyyy HH:mm:ss");
                                }
                            }*/
                        }
                    }

                    result.reset(); // setting the reslut Cursor for Hearder Row index, again to iterate the sheet.
                    //result = parser.parse();
                    while (result.next() )
                    {

                        if ( result.getString("DESCRIPTION/ LOT") != null && !"TOTAL".equalsIgnoreCase(result.getString("DESCRIPTION/ LOT")) )
                        {
                            partNumber = result.getString("MANF. PART NUMBER") == null ? partNumber : result.getString("MANF. PART NUMBER").trim();
                            lotNumber  = result.getString("DESCRIPTION/ LOT").trim();

                            if (lotNumber != null && partNumber != null )
                            {

                                shippedLot = (ShippedLot) m_fgShipDataMap.get(partNumber + lotNumber + shipDate + nextLocation + shipdocNo);
                                if (shippedLot == null)
                                {
                                    shippedLot = new ShippedLot(VENDOR_NAME_SCS, LOCATION_TYPE_FINISHED_GOODS );
                                    m_fgShipDataMap.put(partNumber + lotNumber + shipDate+ nextLocation + shipdocNo, shippedLot);
                                }

                                int quantity = 0;
                                if ( shippedLot.getShipQty() != null )
                                {
                                   try
                                   {
                                       quantity = Integer.parseInt( shippedLot.getShipQty() );
                                   }
                                   catch ( NumberFormatException ignore )
                                   {
                                       quantity = 0;
                                   }
                                   try
                                   {
                                       quantity += result.getInt("QTY");
                                   }
                                   catch ( NumberFormatException ignore )
                                   {
                                       quantity = 0;
                                   }
                                }
                                else
                                {
                                   try
                                   {
                                       quantity = result.getInt("QTY");
                                   }
                                   catch ( NumberFormatException ignore )
                                   {
                                       quantity = 0;
                                   }
                                }
                                if ( quantity > 0 )
                                {
                                    shippedLot.setLotNumber( lotNumber );
                                    shippedLot.setPartNumber( partNumber );
                                    shippedLot.setShipDate( shipDate );
                                    shippedLot.setShipQty( quantity + "");
                                    shippedLot.setShippingDoc( shipdocNo );
                                    shippedLot.setMawb( mawb );
                                    shippedLot.setHawb( hawb );
                                    shippedLot.setCustomerPO( poNumber );
                                    shippedLot.setCustomerSO( soNo );
                                    shippedLot.setFlightNumber( filghtInfo);
                                    shippedLot.setEta( eta );
                                    shippedLot.setEtd( etd );
                                    shippedLot.setNextLocation( nextLocation );
                                    shippedLot.setFabLotNumber( lotNumber );
                                    shippedLot.setProcessType( LOCATION_TYPE_FG );

                                }   
                             }
                        }
                    }
                }
            }
        }
        catch ( Exception ex )
        {
            ex.printStackTrace();
            throw ex;
        }
    } // end of prase parseShipReport method

    private void parseSCKDropShipReport(  String fileName) throws Exception
    {
        if ( fileName == null ) throw new ScalarException( "Filename argument is null" );
        ParserResult result     = null;
        ShippedLot shippedLot   = null;
        String partNumber       = null;
        String lotNumber        = null;
        String shipDate         = null;
        String nextLocation     = null;

        try
        {
            File shipFile = new File( fileName );
            if ( shipFile.exists() == false )
            {
                throw new ScalarException( fileName + " doesnt exist" );
            }

            log( "\nFound " + fileName );
            log( "Parsing the file for Drop Shipment Report..." );
            System.out.println( "\nFound " + fileName );
            System.out.println( "Parsing the file for Drop Shipment Report..." );

            int headerRow = -1;

            Parser parser = ParserFactory.getXLSParser( shipFile );


            ArrayList sheetNamesList = ((XLSParserImpl)parser).getSheetNamesList();

            for(int i= 0; i< sheetNamesList.size(); i++ )
            {
                String sheetName = (String)sheetNamesList.get( i );

                ((XLSParserImpl)parser).setHeaderRowIdx( -1 );
                ((XLSParserImpl)parser).setDataStartRowIdx(0);

                if ( sheetName != null && sheetName.startsWith("PAC") )
                {
                     result = ((XLSParserImpl)parser).parse( sheetName );

                     //Reading the 7th row In the Sheet row index starts from "0"
                    result.moveTo( 9 );

                    try
                    {
                        shipDate = StringUtils.formatDateToString( result.getDate(8) );
                    }catch(RuntimeException re)
                    {
                        shipDate = StringUtils.formatDateToString( result.getDate(9) );
                    }

                    result.moveTo(11);
                    nextLocation = result.getString(6);


                    while ( result.next() )
                    {
                        if ( "MANF. PART NUMBER".equalsIgnoreCase( result.getString(2) ) )
                        {
                            headerRow =  result.getFileRowIdx();
                            break;
                        }
                    }
                    String poNumber = null;
                    String shipdocNo = null;
                    String soNo = null;
                    result.moveTo(headerRow-1);
                    if ( result.getString(0) != null )
                    {
                         poNumber = result.getString(0).substring( result.getString(0).indexOf(":") +1, result.getString(0).length() ).trim();
                    }
                    result.moveTo(headerRow-2);
                    if ( result.getString(0) != null )
                    {
                         shipdocNo = result.getString(0).substring( result.getString(0).indexOf(":")+1, result.getString(0).length() ).trim();
                    }
                    result.moveTo(headerRow-3);
                    if ( result.getString(0) != null )
                    {
                         soNo = result.getString(0).substring( result.getString(0).indexOf("#")+1, result.getString(0).length() ).trim();
                    }

                    ((XLSParserImpl)parser).setHeaderRowIdx(headerRow);
                    ((XLSParserImpl)parser).setDataStartRowIdx(headerRow+1);

                    result = ((XLSParserImpl)parser).parse( sheetName );

                    String mawb = null;
                    String hawb = null;
                    String filghtInfo = null;
                    String etd = null;
                    String eta = null;
                    while( result.next())
                    {
                        if ( result.getString("S/N") != null )
                        {
                            if (  result.getString("S/N").toUpperCase().startsWith("MAWB") )
                            {
                                if ( result.getString("S/N").indexOf(":") != -1 )
                                {
                                    mawb = result.getString("S/N").substring( result.getString("S/N").indexOf(":") +1, result.getString("S/N").length() );
                                }
                                else
                                {
                                   mawb = result.getString("MKT NO.");
                                }
                            }
                            if (  result.getString("S/N").toUpperCase().startsWith("HAWB") )
                            {
                                if ( result.getString("S/N").indexOf(":") != -1 )
                                {
                                    hawb = result.getString("S/N").substring( result.getString("S/N").indexOf(":") +1, result.getString("S/N").length() );
                                }
                                else
                                {
                                   hawb = result.getString("MKT NO.");
                                }
                            }
                            if (  result.getString("S/N").toUpperCase().startsWith("FLT") )
                            {
                                if ( result.getString("S/N").indexOf(":") != -1 )
                                {
                                    filghtInfo = result.getString("S/N").substring( result.getString("S/N").indexOf(":") +1, result.getString("S/N").length() );
                                }
                                else
                                {
                                   filghtInfo = result.getString("MKT NO.");
                                }
                            }
                            /*if (  result.getString("S/N").toUpperCase().startsWith("ETD") )
                            {
                                if ( result.getString("S/N").indexOf(":") != -1 )
                                {
									if(  )
                                    etd = result.getString("S/N").substring( result.getString("S/N").indexOf(":") +1, result.getString("S/N").indexOf(":") + 6 ).trim();
                                    eta = result.getString("S/N").substring( result.getString("S/N").lastIndexOf(":") +1, result.getString("S/N").lastIndexOf(":") +6 ).trim();

                                    etd = StringUtils.formatDate( shipDate + etd, "MM/dd/yyyyHHmm", "MM/dd/yyyy HH:mm:ss");
                                    eta = StringUtils.formatDate( shipDate + eta, "MM/dd/yyyyHHmm", "MM/dd/yyyy HH:mm:ss");
                                }
                            }*/
                        }
                    }

                    result.reset(); // setting the reslut Cursor for Hearder Row index, again to iterate the sheet.
                    //result = parser.parse();
                    while (result.next() )
                    {

                        if ( result.getString("DESCRIPTION/ LOT") != null && !"TOTAL".equalsIgnoreCase(result.getString("DESCRIPTION/ LOT")) )
                        {
                            partNumber = result.getString("MANF. PART NUMBER") == null ? partNumber : result.getString("MANF. PART NUMBER").trim();
                            lotNumber  = result.getString("DESCRIPTION/ LOT").trim();

                            if (lotNumber != null && partNumber != null )
                            {

                                shippedLot = (ShippedLot) m_fgShipDataMap.get(partNumber + lotNumber + shipDate + nextLocation + shipdocNo);
                                if (shippedLot == null)
                                {
                                    shippedLot = new ShippedLot(VENDOR_NAME_SCK, LOCATION_TYPE_FINISHED_GOODS );
                                    m_fgShipDataMap.put(partNumber + lotNumber + shipDate+ nextLocation + shipdocNo, shippedLot);
                                }

                                int quantity = 0;
                                if ( shippedLot.getShipQty() != null )
                                {
                                   try
                                   {
                                       quantity = Integer.parseInt( shippedLot.getShipQty() );
                                   }
                                   catch ( NumberFormatException ignore )
                                   {
                                       quantity = 0;
                                   }
                                   try
                                   {
                                       quantity += result.getInt("QTY");
                                   }
                                   catch ( NumberFormatException ignore )
                                   {
                                       quantity = 0;
                                   }
                                }
                                else
                                {
                                   try
                                   {
                                       quantity = result.getInt("QTY");
                                   }
                                   catch ( NumberFormatException ignore )
                                   {
                                       quantity = 0;
                                   }
                                }
                                if ( quantity > 0 )
                                {
                                    shippedLot.setLotNumber( lotNumber );
                                    shippedLot.setPartNumber( partNumber );
                                    shippedLot.setShipDate( shipDate );
                                    shippedLot.setShipQty( quantity + "");
                                    shippedLot.setShippingDoc( shipdocNo );
                                    shippedLot.setMawb( mawb );
                                    shippedLot.setHawb( hawb );
                                    shippedLot.setCustomerPO( poNumber );
                                    shippedLot.setCustomerSO( soNo );
                                    shippedLot.setFlightNumber( filghtInfo);
                                    shippedLot.setEta( eta );
                                    shippedLot.setEtd( etd );
                                    shippedLot.setNextLocation( nextLocation );
                                    shippedLot.setFabLotNumber( lotNumber );
                                    shippedLot.setProcessType( LOCATION_TYPE_FG );

                                }
                             }
                        }
                    }
                }
            }
        }
        catch ( Exception ex )
        {
            ex.printStackTrace();
            throw ex;
        }
    } // end of prase parseShipReport method

    private void parseSCMDropShipReport(  String fileName) throws Exception
    {
        if ( fileName == null ) throw new ScalarException( "Filename argument is null" );
        ParserResult result     = null;
        ShippedLot shippedLot   = null;
        String partNumber       = null;
        String lotNumber        = null;
        String shipDate         = null;
        String nextLocation     = null;

        try
        {
            File shipFile = new File( fileName );
            if ( shipFile.exists() == false )
            {
                throw new ScalarException( fileName + " doesnt exist" );
            }

            log( "\nFound " + fileName );
            log( "Parsing the file for SCM Drop Shipment Report..." );
            System.out.println( "\nFound " + fileName );
            System.out.println( "Parsing the file for SCM Drop Shipment Report..." );

            int headerRow = -1;

            Parser parser = ParserFactory.getXLSParser( shipFile );

            ((XLSParserImpl)parser).setHeaderRowIdx( -1 );
            ((XLSParserImpl)parser).setDataStartRowIdx(0);

            String mawb = null;
            String hawb = null;
            String filghtInfo = null;
            String etd = null;
            String eta = null;
            String poNumber = null;
            String shipdocNo = null;
            String soNo = null;
            String invoiceNo    = null;
            
            result = ((XLSParserImpl)parser).parse();

             //Reading the 12th row In the Sheet row index starts from "0"
            result.moveTo( 11 );

            nextLocation = result.getString(1);
            invoiceNo    = result.getString(4);

            result.moveTo(12);
            mawb    = result.getString(4);
            result.moveTo(13);
            hawb    = result.getString(4);
            result.moveTo(14);
            filghtInfo = result.getString(4);

            result.moveTo(16);
            etd = (result.getString(4) != null && !"N/A".equalsIgnoreCase( result.getString(4) ) ) ? result.getString(4).split("/")[0] : null;
            etd = StringUtils.formatDate(etd, "dd-MMM-yyyy", "MM/dd/yyyy" );

            result.moveTo(17);
            eta = (result.getString(4) != null && !"N/A".equalsIgnoreCase( result.getString(4) ) ) ? result.getString(4).split("/")[0] : null;

            eta = StringUtils.formatDate(eta, "dd-MMM-yyyy", "MM/dd/yyyy" );

            result.moveTo(18);
            shipDate = result.getString(4);
            if ( shipDate != null )
            {
                shipDate = StringUtils.formatDate(shipDate, "dd-MMM-yyyy", "MM/dd/yyyy" );
            }
            result.moveTo(19);
            poNumber = result.getString(4);
            
            result.moveTo(21);
            soNo = result.getString(1);
            result.moveTo(22);
            shipdocNo = result.getString(1);
            while ( result.next() )
            {
                if ( result.getString(4) != null && "MANUFACTURING ITEM".equalsIgnoreCase( result.getString(4).trim() ) )
                {
                    headerRow =  result.getFileRowIdx();
                    break;
                }
            }

            ((XLSParserImpl)parser).setHeaderRowIdx(headerRow);
            ((XLSParserImpl)parser).setDataStartRowIdx(headerRow+1);

            result = ((XLSParserImpl)parser).parse();
            
            while (result.next() )
            {
                if ( result.getString(" MANUFACTURING ITEM") != null && result.getString(" MANUFACTURING ITEM").trim().length()> 0
                        && result.getString("CUSTOMER\nLOT Number") != null && result.getString("CUSTOMER\nLOT Number").trim().length() > 0 )
                {
                    partNumber = result.getString(" MANUFACTURING ITEM").trim();
                    lotNumber  = result.getString("CUSTOMER\nLOT Number").trim();

                    shippedLot = (ShippedLot) m_fgShipDataMap.get(partNumber + lotNumber + shipDate + nextLocation + shipdocNo);
                    if (shippedLot == null)
                    {
                        shippedLot = new ShippedLot(VENDOR_NAME_SCM, LOCATION_TYPE_FINISHED_GOODS );
                        m_fgShipDataMap.put(partNumber + lotNumber + shipDate+ nextLocation + shipdocNo, shippedLot);
                    }

                    int quantity = 0;
                    if ( shippedLot.getShipQty() != null )
                    {
                       try
                       {
                           quantity = Integer.parseInt( shippedLot.getShipQty() );
                       }
                       catch ( NumberFormatException ignore )
                       {
                           quantity = 0;
                       }
                       try
                       {
                           quantity += result.getInt("QUANTITY");
                       }
                       catch ( NumberFormatException ignore )
                       {
                           quantity = 0;
                       }
                    }
                    else
                    {
                       try
                       {
                           quantity = result.getInt("QUANTITY");
                       }
                       catch ( NumberFormatException ignore )
                       {
                           quantity = 0;
                       }
                    }
                    if ( quantity > 0 )
                    {
                        shippedLot.setLotNumber( lotNumber );
                        shippedLot.setPartNumber( partNumber );
                        shippedLot.setShipDate( shipDate );
                        shippedLot.setShipQty( quantity + "");
                        shippedLot.setShippingDoc( shipdocNo );
                        shippedLot.setMawb( "N/A".equalsIgnoreCase( mawb ) ? null : mawb );
                        shippedLot.setHawb( "N/A".equalsIgnoreCase( hawb ) ? null : hawb );
                        shippedLot.setCustomerPO( poNumber );
                        shippedLot.setCustomerSO( soNo );
                        shippedLot.setFlightNumber( filghtInfo);
                        shippedLot.setEta( eta );
                        shippedLot.setEtd( etd );
                        shippedLot.setInvoiceNumber( invoiceNo);
                        shippedLot.setNextLocation( nextLocation );
                        shippedLot.setFabLotNumber( lotNumber );
                        shippedLot.setProcessType( LOCATION_TYPE_FG );
                    }
                }
            }
        }
        catch ( Exception ex )
        {
            ex.printStackTrace();
            throw ex;
        }
    } // end of prase parseShipReport method

   private static void log( String message )
    {

        if ( message == null ) return;

        if ( m_parserLogger != null )
        {
            synchronized ( m_parserLogger )
            {
                m_parserLogger.logDebug( message + LINE_SEPARATOR );
            }
        }
        else
        {
            System.out.println( message );
        }
    }
}
